PostgreSQL pg_profile 数据导入和导出

1 背景知识

本文介绍如何导出和导入 pg_profile 已收集的统计信息数据。

收集的快照统计数据可以进行导入导出。用于服务器实例迁移，还可以将收集的数据发送给支持团队，作为性能问题分析的定位的一手资料。

2 数据导出

通过函数 export_data() 、COPY 等任何方式导出数据。

默认情况下，export_data() 函数将导出所有的服务器的采集快照。也可以只导出一个服务器，并进一步限制快照范围。

su - postgres  
psql -U postgres -d testdb 

\copy (select * from profile.export_data('omega')) to 'export.csv'

COPY 46598

想要更多信息请参考 export 函数的章节。

3 数据导入

1. 登录新的数据库。

su - postgres  
psql -U postgres -d testdb 

2. 统计数据只能从本地表导入，所以先要加载导出的数据到本地表中。

CREATE TABLE import (section_id bigint, row_data json);

\copy import from 'export.csv'

CREATE TABLE
COPY 6437

3. 现在可以执行数据导入，将本地表 import 传参到 import_data() 函数。并为 服务器连接 设置前缀，以防与现有的 服务器连接 所冲突。

SELECT * FROM profile.import_data('import','remote_');

所有服务器连接都会被禁止启用。

如果想要新追加数据，请不要设置此函数的服务器连接的前缀(server_name_prefix)参数。
如果想要重命名服务器连接 ，请设置前缀(server_name_prefix)参数，以区别不同的统计信息数据。

想要更多信息请参考 import 的章节。

4 数据验证

4.1 查看导入的快照

SELECT * FROM profile.show_samples('remote_omega') limit 10;

-[ RECORD 1 ]---+-----------------------
sample          | 59
sample_time     | 2024-06-28 15:30:02+08
sizes_collected | t
dbstats_reset   | 
bgwrstats_reset | 
archstats_reset | 
-[ RECORD 2 ]---+-----------------------
sample          | 60
sample_time     | 2024-06-28 16:00:02+08
sizes_collected | t
dbstats_reset   | 
bgwrstats_reset | 
archstats_reset | 
-[ RECORD 3 ]---+-----------------------
sample          | 61
sample_time     | 2024-06-28 16:30:02+08
sizes_collected | t
dbstats_reset   | 
bgwrstats_reset | 
archstats_reset | 
-[ RECORD 4 ]---+-----------------------
sample          | 62
sample_time     | 2024-06-28 17:00:01+08
sizes_collected | t
dbstats_reset   | 
bgwrstats_reset | 
archstats_reset | 
-[ RECORD 5 ]---+-----------------------
sample          | 63
sample_time     | 2024-06-28 17:30:01+08
sizes_collected | t
dbstats_reset   | 
bgwrstats_reset | 
archstats_reset | 

4.2 查看服务器链接

SELECT * FROM profile.show_servers();

-[ RECORD 1 ]--+------------
server_name    | local
connstr        | dbname=postgres port=5432
enabled        | t
max_sample_age | 
description    | 
-[ RECORD 2 ]--+------------
server_name    | remote_omega
connstr        | host=192.168.10.155  user=postgres dbname=testdb password=postgres port=5432
enabled        | f
max_sample_age | 
description    | 

5 环境清理

1. 删除 import 表。

DROP TABLE import ;

2. 删除 remote_omega 相关的服务器连接和所有快照。

SELECT profile.drop_server('remote_omega');